﻿using LituoPro.database.entity;
using LituoPro.database.entity.bs;
using LituoPro.database.entity.req;
using LituoPro.database.entity.res;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Xml.Linq;

namespace LituoPro.database
{
    public class SqlLiteDataHelper
    {
        private string dataSource = "database//lt.db;Version=3";

        public void InsertLtRetMessageData(LtRetMessageEntity ltRetMessage)
        {
            string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, dataSource);
            string dburl = "Data Source=" + filePath;
            using (var connection = new SQLiteConnection(dburl))
            {
                connection.Open();
                // 如果没有则创建一个新的表
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS lt_ret_message (Id TEXT PRIMARY KEY,event_id TEXT, dev_type TEXT, ai_content TEXT,ai_content TEXT,di_content TEXT,ao_content TEXT,do_content TEXT,create_time TEXT);";
                    command.ExecuteNonQuery();
                }
                string sql = "INSERT INTO lt_ret_message (id,event_id,dev_type,ai_content,di_content,ao_content,do_content,create_time) VALUES (@id, @event_id,@dev_type,@ai_content,@di_content,@ao_content,@do_content,@create_time)";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@id", ltRetMessage.id);
                    command.Parameters.AddWithValue("@event_id", ltRetMessage.eventId);
                    command.Parameters.AddWithValue("@dev_type", ltRetMessage.devType);
                    command.Parameters.AddWithValue("@ai_content", ltRetMessage.aiContent);
                    command.Parameters.AddWithValue("@di_content", ltRetMessage.diContent);
                    command.Parameters.AddWithValue("@ao_content", ltRetMessage.aoContent);
                    command.Parameters.AddWithValue("@do_content", ltRetMessage.doContent);
                    command.Parameters.AddWithValue("@create_time", ltRetMessage.createTime);
                    command.ExecuteNonQuery(); // 添加数据
                }
                connection.Close();
            }
        }


        public List<QueryDataRes> QueryDataPage(QueryDataReq queryDataReq, BasePage basePage)
        {
            List<QueryDataRes> queryData = new List<QueryDataRes>();
            string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, dataSource);
            string dburl = "Data Source=" + filePath;
            using (var connection = new SQLiteConnection(dburl))
            {
                connection.Open();
                // 如果没有则创建一个新的表
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS lt_ret_message (Id TEXT PRIMARY KEY,event_id TEXT, dev_type TEXT, ai_content TEXT,ai_content TEXT,di_content TEXT,ao_content TEXT,do_content TEXT,create_time TEXT);";
                    command.ExecuteNonQuery();
                }
                string sql = "SELECT * FROM lt_ret_message  order by create_time DESC LIMIT @pageSize OFFSET @offset";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@pageSize", basePage.pageSize);
                    command.Parameters.AddWithValue("@offset", basePage.offset);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            QueryDataRes queryDataEntity = new QueryDataRes();
                            string eventId = reader["event_id"].ToString();
                            string devType = reader["dev_type"].ToString();
                            string aiContent = reader["ai_content"].ToString();
                            string diContent = reader["di_content"].ToString();
                            string aoContent = reader["ao_content"].ToString();
                            string doContent = reader["do_content"].ToString();
                            string createTime = reader["create_time"].ToString();
                            queryDataEntity.eventId = eventId;
                            queryDataEntity.devType = devType;
                            queryDataEntity.aiContent = aiContent;
                            queryDataEntity.diContent = diContent;
                            queryDataEntity.aoContent = aoContent;
                            queryDataEntity.doContent = doContent;
                            queryDataEntity.createTime = createTime;
                            queryData.Add(queryDataEntity);
                        }
                    }
                }
                connection.Close();
            }
            return queryData;
        }




        public long QueryDataCount(QueryDataReq queryDataReq)
        {
            long totalCount = 0;
            string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, dataSource);
            string dburl = "Data Source=" + filePath;
            using (var connection = new SQLiteConnection(dburl))
            {
                connection.Open();
                // 如果没有则创建一个新的表
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS lt_ret_message (Id TEXT PRIMARY KEY,event_id TEXT, dev_type TEXT, ai_content TEXT,ai_content TEXT,di_content TEXT,ao_content TEXT,do_content TEXT,create_time TEXT);";
                    command.ExecuteNonQuery();
                }
                string sql = "SELECT count(*) FROM lt_ret_message";
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    totalCount = (long)command.ExecuteScalar();
                }
                connection.Close();
            }
            return totalCount;
        }

        public List<QueryAiDataRes> QueryAiData()
        {
            List<QueryAiDataRes> queryDataList = new List<QueryAiDataRes>();
            string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, dataSource);
            string dburl = "Data Source=" + filePath;
            using (var connection = new SQLiteConnection(dburl))
            {
                connection.Open();
                // 如果没有则创建一个新的表
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS lt_ret_message (Id TEXT PRIMARY KEY,event_id TEXT, dev_type TEXT, ai_content TEXT,ai_content TEXT,di_content TEXT,ao_content TEXT,do_content TEXT,create_time TEXT);";
                    command.ExecuteNonQuery();
                }
                string sql = "SELECT * FROM lt_ret_message where dev_type = '62' order by create_time DESC LIMIT 15";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            QueryAiDataRes queryAiDataRes = new QueryAiDataRes();
                            string aiData = reader["ai_content"].ToString();
                            queryAiDataRes.aiContent = aiData;
                            string createTime = reader["create_time"].ToString();
                            queryAiDataRes.createTime = createTime;
                            queryDataList.Add(queryAiDataRes);
                        }
                    }
                }
                connection.Close();
            }
            return queryDataList;
        }





    }
}
